package edu.arizona.mindseye.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import edu.arizona.mindseye.Annotation;
import edu.arizona.mindseye.PropertiesManager;
import edu.arizona.mindseye.event.Event;
import edu.arizona.mindseye.event.EventListener;
import edu.arizona.mindseye.event.EventManager;
import edu.arizona.mindseye.event.enums.EventType;
import edu.arizona.mindseye.event.enums.Parameter;

public class Database {

	private Connection _conn;
	
	public Database() { 
		connect();
		
		EventManager.inst().register(EventType.ANNOTATIONS, new EventListener() {
			@Override
			public void onEvent(Event e) {
				String movieName = (String) e.getValue(Parameter.name);
				List<Annotation> annotations = (List<Annotation>) e.getValue(Parameter.annotation);
				save(movieName, annotations);
			} 
		});
		
		EventManager.inst().register(EventType.MOVIE_LOADED, new EventListener() {
			@Override
			public void onEvent(Event e) {
				if (_conn != null)
					close();
				connect();
				
				Event event = new Event(EventType.BEHAVIORS);
				event.addParameter(Parameter.behaviors, getBehaviors());
				EventManager.inst().dispatch(event);
			} 
		});
		
		EventManager.inst().register(EventType.ADD_BEHAVIOR, new EventListener() {
			@Override
			public void onEvent(Event e) {
				addBehavior((String) e.getValue(Parameter.name));
				
				Event event = new Event(EventType.BEHAVIORS);
				event.addParameter(Parameter.behaviors, getBehaviors());
				EventManager.inst().dispatch(event);
			} 
		});
		
	}
	
	public void connect() { 
		try {
			Class.forName ("com.mysql.jdbc.Driver").newInstance();

			String userName = PropertiesManager.get().getProperty("username");
			String password = PropertiesManager.get().getProperty("password");
			
			String database = PropertiesManager.get().getProperty("database");
			String url = "jdbc:mysql://" + database;
			_conn = DriverManager.getConnection (url, userName, password);
			System.out.println ("Database connection established");
		} catch (Exception e) {
			System.err.println ("Cannot connect to database server");
		} 
	}
	
	public void close() { 
		try { 
			_conn.close();
		} catch (Exception e) { 
			e.printStackTrace();
		}
	}
	
	public int add(String movie) { 
		int id = 0;
		
		try { 
			Statement s = _conn.createStatement();
			ResultSet rs = s.executeQuery("select max(id) from movies");
			if (rs.next()) { 
				id = rs.getInt(1) + 1;
			} 
			rs.close();
			
			s.executeUpdate("insert into movies (id, movie_name) values (" + id + ",'" + movie + "')");
			s.close();
		} catch (Exception e) { 
			e.printStackTrace();
		}
		return id;
	}

	/**
	 * Query the database in order to get the movie id for
	 * the given movie.
	 * @return
	 */
	public int getMovieId(String movieName) { 
		int id = -1;
		try { 
			Statement s = _conn.createStatement();
			ResultSet rs = s.executeQuery("select id from movies where movie_name = '" + movieName + "'");
			if (rs.next()) { 
				id = rs.getInt("id");
			} 
			rs.close();
			s.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if (id == -1) 
			return add(movieName);
		return id;
	}
	
	/**
	 * Remove all of the entries for the given movie.
	 * @param movieName
	 */
	public void clear(int movieId) { 
		try { 
			Statement s = _conn.createStatement();
			s.executeUpdate("delete from annotations where movie_id = " + movieId);
			s.close();
		} catch (Exception e) { 
			e.printStackTrace();
		}
	}
	
	/**
	 * Save all of the bits of information about the movie.
	 * @param movieName
	 * @param annotations
	 */
	public void save(String movieName, List<Annotation> annotations) {		
		int movieId = getMovieId(movieName);
		clear(movieId);

		try { 
			PreparedStatement ps = _conn.prepareStatement(
					"insert into annotations (movie_id, human_id, start, end, behaviors) " +
					"values (?, ?, ?, ?, ?)");
			
			for (Annotation a : annotations) { 
				ps.setInt(1, movieId);
				ps.setInt(2, a.humanId());
				ps.setInt(3, a.min());
				ps.setInt(4, a.max());
				ps.setString(5, a.behaviorsString());
				ps.execute();
			}
			
			ps.close();
			
			EventManager.inst().dispatch(new Event(EventType.SAVE_SUCCESSFUL));
			
		} catch (Exception e) { 
			e.printStackTrace();
		}
	}
	
	public List<String> getBehaviors() { 
		List<String> set = new ArrayList<String>();
		try { 
			Statement s = _conn.createStatement();
			ResultSet rs = s.executeQuery("select name from behavior order by name");
			while (rs.next())
				set.add(rs.getString(1));
			rs.close();
			s.close();
		} catch (Exception e) { 
			e.printStackTrace();
		}
		return set;
	}
	
	public void addBehavior(String name) { 
		try { 
			Statement s = _conn.createStatement();
			s.executeUpdate("insert into behavior (name) values ('" + name + "')");
		} catch (Exception e) { 
			e.printStackTrace();
		}
	}
	
	public Set<String> getFinishedMovies() { 
		Set<String> set = new HashSet<String>();
		try { 
			Statement s = _conn.createStatement();
			ResultSet rs = s.executeQuery("select distinct m.movie_name from movies m, annotations a where m.id = a.movie_id");
			while (rs.next())
				set.add(rs.getString(1));
			rs.close();
			s.close();
		} catch (Exception e) { 
			e.printStackTrace();
		}
		return set;
	}
	
	public boolean done(String movieName) { 
		boolean found = false;
		try { 
			Statement s = _conn.createStatement();
			ResultSet rs = s.executeQuery("select m.movie_name from movies m, annotations a " +
					"where m.id = a.movie_id and movie_name = '" + movieName + "'");
			if (rs.next())
				found = true;
			rs.close();
			s.close();
		} catch (Exception e) { 
			e.printStackTrace();
		}
		return found;
	}
}
